package cn.com.fssg.platform.base.handler.impl.gsl.zw;

import cn.com.fssg.platform.base.handler.InterfaceHandler;
import cn.com.fssg.platform.biz.BizUtil;
import cn.com.fssg.platform.biz.ExtendUtil;
import cn.hutool.crypto.SmUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.crypto.Cipher;
import javax.crypto.spec.SecretKeySpec;
import javax.servlet.http.HttpServletRequest;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.time.format.DateTimeFormatter;
import java.util.*;

/**
 * @Description: 工商联执委导出接口
 */
@Component
public class GslJcZwImportExcelCtl implements InterfaceHandler {

    private NamedParameterJdbcTemplate jdbcTemplate = BizUtil.cur().tenantMainJdbcTemplate();
//    @Autowired
//    private NamedParameterJdbcTemplate jdbcTemplate;

    @Override
    public Object run(HttpServletRequest request, JSONObject jsonObject) throws Exception {
        List<String> res = new ArrayList<>();
        try {
            /*读取导入的excel文件*/
            MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
            Iterator<String> iter = multiRequest.getFileNames();
            MultipartFile file = null;
            while (iter.hasNext()) {
                file = multiRequest.getFile(iter.next());
            }
            if (file == null) {
                return "导入失败";
            }
            Map<String, Object> maps = this.getExcel(file.getInputStream());
            List<Map<String, Object>> resultList = (List<Map<String, Object>>) maps.get("resultList");
            List<Map<String, Object>> zwList = this.getExcelData(resultList);
//            String str = "[{\"phone\":\"13531312353\",\"rz_date\":1761095488000,\"company\":\"测试测试\",\"job_state\":\"1\",\"username\":\"测试1021\",\"gsl_post\":\"0\"}]";
//            List<Map<String, Object>> zwList = JSONObject.parseObject(str, List.class);
            res.add(JSONObject.toJSONString(zwList));
            //保存工商联个人信息
            for (Map<String, Object> map : zwList) {
                res.add(JSONObject.toJSONString(jsonObject));
                res.add(JSONObject.toJSONString(map));
                JSONObject json = new JSONObject();
                json.put("jcId", jsonObject.getString("jcId"));
                json.put("name", map.get("username"));
                json.put("phone", map.get("phone"));
                json.put("gsl_post", map.get("gsl_post"));
                json.put("job_state", map.get("job_state"));
                json.put("company", map.get("company"));
                json.put("rz_date", map.get("rz_date"));

                res.add(JSONObject.toJSONString(json));

                String queryInfoSql = "select * from gsl_zw_info where phone = :phone";
                Map<String, Object> infoParam = new HashMap<>();
                String phoneNum = encrypt((String) map.get("phone"), "ankon");
                infoParam.put("phone", phoneNum);
                List<Map<String, Object>> zwInfoList = jdbcTemplate.queryForList(queryInfoSql, infoParam);
                res.add(JSONObject.toJSONString(zwInfoList));
                if(!zwInfoList.isEmpty()) {
                    String zwId = (String) zwInfoList.get(0).get("zw_id");
                    json.put("id", zwInfoList.get(0).get("id"));
                    String queryQySql = "select * from gsl_zw_qy where zw_id = :zwId";
                    Map<String, Object> qyParam = new HashMap<>();
                    qyParam.put("zwId", zwId);
                    List<Map<String, Object>> qyInfoList = jdbcTemplate.queryForList(queryQySql, qyParam);
                    if(!qyInfoList.isEmpty()) {
                        json.put("qyId", qyInfoList.get(0).get("id"));
                    }
                }
                String result = this.checkForm(json);
                if(result != null) {
                    return result;
                }
                String gslZwQyId = StringUtils.isEmpty(json.getString("qyId")) ? BizUtil.cur().getSnowflakeIdStr() : json.getString("qyId");
                json.put("gslZwQyId", gslZwQyId);
                //更新届次执委人数
                String jcId = json.getString("jcId");
                res.add(jcId);
                //保存工商联执委人员信息
                saveGslZwInfo(json);
                res.add(JSONObject.toJSONString(json));
                String sql = "select id from gsl_jc_zw_user where gsl_session = :jcId";
                Map<String, Object> param = new HashMap<>();
                param.put("jcId", jcId);
                List<Map<String, Object> > jcUserList = jdbcTemplate.queryForList(sql, param);
                if(!jcUserList.isEmpty()) {
                    String updateSql = "UPDATE gsl_jc SET zw_count = :zwCount WHERE id = :jcId";
                    param.put("zwCount", jcUserList.size());
                    jdbcTemplate.update(updateSql, param);
                }
                res.add(JSONObject.toJSONString(param));
            }
            res.add("可以成功保存数据");
        } catch (Exception e) {
            res.add(e.toString());
//            throw e;
        }
        return res;
    }

    public Map<String, Object> getExcel(InputStream is) {
        List<Map<String, Object>> resultList = new ArrayList<>();
        List<String> headers = new ArrayList<>();

        // 使用静态内部类替代匿名内部类
        ExcelDataListener listener = new ExcelDataListener(resultList, headers);

        EasyExcel.read(is, listener).sheet().doRead();

        Map<String, Object> result = new HashMap<>();
        result.put("headers", headers);
        result.put("resultList", resultList);
        return result;
    }

    // 静态内部类
    public static class ExcelDataListener extends AnalysisEventListener<Map<Integer, String>> {
        private final List<Map<String, Object>> resultList;
        private final List<String> headers;

        public ExcelDataListener(List<Map<String, Object>> resultList, List<String> headers) {
            this.resultList = resultList;
            this.headers = headers;
        }

        @Override
        public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
            headers.clear();
            headMap.values().forEach(headers::add);
            System.out.println("表头: " + headers);
        }

        @Override
        public void invoke(Map<Integer, String> data, AnalysisContext context) {
            Map<String, Object> rowMap = new HashMap<>();
            for (Map.Entry<Integer, String> entry : data.entrySet()) {
                String columnName = entry.getKey() < headers.size() ?
                        headers.get(entry.getKey()) : "Column_" + (entry.getKey() + 1);
                rowMap.put(columnName, entry.getValue());
            }
            resultList.add(rowMap);
            System.out.println("数据: " + rowMap);
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            System.out.println("解析完成，共" + resultList.size() + "行");
        }
    }

    public List<Map<String, Object>> getExcelData(List<Map<String, Object>> excelList) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();
        if(!excelList.isEmpty()) {
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            for (Map<String, Object> excelMap : excelList) {
                Map<String, Object> dataMap = new HashMap<>();
                String gslPost = (String) excelMap.get("工商联职务");
                String jobState = excelMap.get("状态").toString();

                String rzDate = (String) excelMap.get("任职时间");
                LocalDateTime localDateTime = LocalDateTime.parse(rzDate, formatter);

                dataMap.put("username", excelMap.get("执委姓名"));
                dataMap.put("rz_date", localDateTime.toInstant(ZoneOffset.of("+8")).toEpochMilli());
                dataMap.put("gsl_post", StringUtils.isEmpty(gslPost) ? "" : selectGslPost(gslPost));
                dataMap.put("company", excelMap.get("单位职位"));
                dataMap.put("phone", excelMap.get("手机号码"));
                dataMap.put("job_state", "在职".equals(jobState) ? "1" : "2");

                result.add(dataMap);
            }
        }
        return result;
    }

    private String selectGslPost(String post) {
        String gslPost = "";
        switch (post) {
            case "主席":
                gslPost = "0";
                break;
            case "副主席":
                gslPost = "1";
                break;
            case "副会长":
                gslPost = "2";
                break;
            case "秘书长":
                gslPost = "3";
                break;
            case "常委":
                gslPost = "4";
                break;
            case "执委":
                gslPost = "5";
                break;
        }
        return gslPost;
    }

    /** 验证表单数据 */
    public String checkForm(JSONObject jsonObject) throws Exception {
        String jcId = jsonObject.getString("jc_id");
        String phone = jsonObject.getString("phone");
        if(StringUtils.isEmpty(phone)) {
            return "请填写手机号码";
        }
        return null;
    }

    /** 保存工商联执委个人/企业信息 */
    public int saveGslZwInfo(JSONObject jsonObject) throws Exception {
        String gslZwId = jsonObject.getString("id");
        if(StringUtils.isEmpty(gslZwId)) {
            gslZwId = BizUtil.cur().getSnowflakeIdStr();
            jsonObject.put("id", gslZwId);
        }
        String querySql = "select * from gsl_zw_info where id = :id";
        Map<String, Object> param = new HashMap<>();
        param.put("id", gslZwId);
        List<Map<String, Object>> list = jdbcTemplate.queryForList(querySql, param);
        if(!list.isEmpty()){
            //存在历史执委信息，获取历史执委信息中的执委信息
            return updateGslZwInfo(list.get(0), jsonObject);
        } else {
            //验证当前登录用户是否为执委
            String wxUserId = BizUtil.cur().getSnowflakeIdStr();
            //不存在历史执委信息
            return insertGslZwInfo(jsonObject, wxUserId);
        }
    }

    /** 编辑工商联执委个人/企业信息 */
    public int updateGslZwInfo(Map<String, Object> oldZw, JSONObject jsonObject) throws Exception {
        String wxUserId = (String) oldZw.get("zw_id");
        //获取wx_user信息
        String queryWxUserSql = "select * from wx_user where id = :id";
        Map<String, Object> wxUserParam = new HashMap<>();
        wxUserParam.put("id", wxUserId);
        List<Map<String, Object>> wxUserLists = jdbcTemplate.queryForList(queryWxUserSql, wxUserParam);
        Map<String, Object> oldUser = wxUserLists.get(0);

        String phone = jsonObject.getString("phone");
        String encryptPhone = encrypt(phone, "userltokon");
        oldUser.put("phone", encryptPhone);
        oldUser.put("login_name", encryptPhone);
        if(!Objects.isNull(jsonObject.getString("name"))){
            String userName = jsonObject.getString("name");
            String encryptUserName = encrypt(userName, "usertokon");
            oldUser.put("username", encryptUserName);
        }

        //保存gsl_zw_info
        Map<String, Object> zwInfoParam = getGslZwInfo(jsonObject);
        if(!zwInfoParam.isEmpty()){
            updateDatabase(zwInfoParam, "gsl_zw_info");
        }
        //保存gsl_zw_qy
        Map<String, Object> zwQyParam = getGslZwQy(jsonObject);
        if(!zwQyParam.isEmpty()){
            updateDatabase(zwQyParam, "gsl_zw_qy");
        }
        return 0;

    }

    /** 新增工商联执委个人/企业信息 */
    public int insertGslZwInfo(JSONObject jsonObject, String wxUserId) throws Exception {
        String jcId = jsonObject.getString("jcId");
        //保存wx_user表数据
        Map<String, Object> wxUser = new HashMap<>();
        wxUser.put("gsl_session", jcId);
        wxUser.put("id", wxUserId);
        wxUser.put("update_by", BizUtil.cur().curUserId());
        wxUser.put("create_by", BizUtil.cur().curUserId());
        wxUser.put("update_date", new Date());
        wxUser.put("create_date", new Date());
        wxUser.put("del_flag", "0");
        wxUser.put("role_scope", "1");
        wxUser.put("parent_role_scope", "1");
        wxUser.put("login_flag", "0");
        wxUser.put("type", "0");
        wxUser.put("job_state", "1");
        String loginName = jsonObject.getString("phone");
        String encryptLoginName = encrypt(loginName, "userltokon");
        wxUser.put("login_name", encryptLoginName);
        wxUser.put("phone", encryptLoginName);
        String userName = jsonObject.getString("name");
        String encryptUserName = encrypt(userName, "usertokon");
        wxUser.put("username", encryptUserName);
        String password = loginName + ":" + "123456";
        wxUser.put("password", SmUtil.sm3(password));//这里需要加密
        //保存wx_user数据
        insertDatabase(wxUser, "wx_user");

        if(ExtendUtil.cur().checkOnlyUser(loginName)) {
            //保存平台用户
            JSONObject userJson = new JSONObject();
            userJson.put("id", wxUserId);
            userJson.put("loginname", loginName);
            userJson.put("realname", userName);
            userJson.put("password", SmUtil.sm3(password));
            userJson.put("status", 1);
            userJson.put("updateBy", BizUtil.cur().curUserId());
            userJson.put("createBy", BizUtil.cur().curUserId());
            userJson.put("updateTime", new Date());
            userJson.put("createTime", new Date());
            userJson.put("selectedroles", "1976856675140206593");//执委角色

            ExtendUtil.cur().addUser(userJson);
        }

        //获取届次信息
        String queryJcSql = "select * from gsl_jc where id = :id";
        Map<String, Object> jcParam = new HashMap<>();
        jcParam.put("id", jcId);
        List<Map<String, Object>> jcLists = jdbcTemplate.queryForList(queryJcSql, jcParam);
        if(jcLists.isEmpty()){
            throw new Exception("执委届次信息获取失败，请刷新重试");
        }
        Map<String, Object> gslJcZwParam = new HashMap<>();
        gslJcZwParam.put("jc_id", jcId);
        gslJcZwParam.put("zw_id", wxUserId);
        gslJcZwParam.put("id", BizUtil.cur().getSnowflakeIdStr());
        gslJcZwParam.put("update_by", BizUtil.cur().curUserId());
        gslJcZwParam.put("create_by", BizUtil.cur().curUserId());
        gslJcZwParam.put("update_date", new Date());
        gslJcZwParam.put("create_date", new Date());
        gslJcZwParam.put("del_flag", "0");
        //保存gsl_jc_zw表
        insertDatabase(gslJcZwParam, "gsl_jc_zw");

        //保存gsl_zw_info
        jsonObject.put("zw_id", wxUserId);
        Map<String, Object> zwInfoParam = getGslZwInfo(jsonObject);
        insertDatabase(zwInfoParam, "gsl_zw_info");

        //保存gsl_zw_qy
        Map<String, Object> zwQyParam = getGslZwQy(jsonObject);
        return insertDatabase(zwQyParam, "gsl_zw_qy");
    }

    /** 更新数据库数据 */
    public int updateDatabase(Map<String, Object> param, String tableName) {
        List<String> fields = new ArrayList<>();
        param.forEach((k, v) -> {
            if (v != null && !"id".equals(k)) {
                fields.add(k + " = :" + k);
            }
        });

        String sql = String.format("UPDATE %s SET %s WHERE id = '%s'",
                tableName,
                String.join(", ", fields),
                param.get("id")
        );
        if(!fields.isEmpty()){
            return jdbcTemplate.update(sql, param);
        }
        return 0;
    }

    /** 新增数据库数据 */
    public int insertDatabase(Map<String, Object> param, String tableName) {
        List<String> fields = new ArrayList<>();
        List<String> placeholders = new ArrayList<>();
        param.forEach((k, v) -> {
            if (v != null) {
                fields.add(k);
                placeholders.add(":" + k);
            }
        });

        String sql = String.format("INSERT INTO %s (%s) VALUES (%s)",
                tableName,
                String.join(", ", fields),
                String.join(", ", placeholders)
        );
        return jdbcTemplate.update(sql, param);
    }

    /** 获取执委个人信息所需保存的数据 */
    public Map<String, Object> getGslZwInfo(JSONObject jsonObject) {
        Map<String, Object> result = new HashMap<>();
        mainFieldList.forEach(key -> {
            if(!Objects.isNull(jsonObject.get(key))) {
                if("name".equals(key)) {
                    //姓名，需要加密存储
                    try {
                        String encName = encrypt((String) jsonObject.get(key), "nametokon");
                        result.put(key, encName);
                    } catch (Exception e) {
                        result.put(key, jsonObject.get(key));
                    }
                } else if("phone".equals(key)) {
                    //手机号码，需要加密存储
                    try {
                        String encName = encrypt((String) jsonObject.get(key), "ankon");
                        result.put(key, encName);
                    } catch (Exception e) {
                        result.put(key, jsonObject.get(key));
                    }
                } else {
                    result.put(key, jsonObject.get(key));
                }
            }
        });
        return result;
    }
    /** 获取执委企业信息所需保存的数据 */
    public Map<String, Object> getGslZwQy(JSONObject jsonObject) {
        Map<String, Object> result = new HashMap<>();
        enterpriseFieldList.forEach(key -> {
            if(!Objects.isNull(jsonObject.get(key))) {
                result.put(key, jsonObject.get(key));
            }
        });
        result.put("id", jsonObject.get("gslZwQyId"));
        return result;
    }

    public static final String ALGORITHM = "AES";

    // 密钥补全
    public static byte[] padKey(byte[] key, int length) {
        return Arrays.copyOf(key, length);
    }

    /**
     * 字节数组转HEX字符串（替代Base64）
     */
    public static String bytesToHex(byte[] bytes) {
        StringBuilder hex = new StringBuilder(bytes.length * 2);
        for (byte b : bytes) {
            hex.append(String.format("%02X", b));
        }
        return hex.toString();
    }

    /** 数据加密 */
    public static String encrypt(String input, String keys) throws Exception {
        // 1. 补全密钥为16字节（AES-128）
        byte[] keyBytes = padKey(keys.getBytes(StandardCharsets.UTF_8), 16);
        SecretKeySpec key = new SecretKeySpec(keyBytes, "AES");

        // 2. 初始化加密器
        Cipher cipher = Cipher.getInstance(ALGORITHM);
        cipher.init(Cipher.ENCRYPT_MODE, key);

        // 3. 加密并转为HEX字符串
        byte[] encryptedBytes = cipher.doFinal(input.getBytes(StandardCharsets.UTF_8));
        return bytesToHex(encryptedBytes);
    }

    /**
     * HEX 字符串转字节数组
     */
    public static byte[] hexToBytes(String hex) {
        byte[] bytes = new byte[hex.length() / 2];
        for (int i = 0; i < bytes.length; i++) {
            bytes[i] = (byte) Integer.parseInt(hex.substring(i * 2, i * 2 + 2), 16);
        }
        return bytes;
    }

    public static final List<String> mainFieldList = Arrays.asList(
            "id",
            "name",
            "company",
            "sex",
            "phone",
            "nation",
            "postal_address",
            "birthday",
            "name_of_agent",
            "nationality",
            "gent_account_number",
            "native_place",
            "position",
            "birthplace",
            "academic_degree",
            "education",
            "honekong_id",
            "id_card",
            "is_cadre",
            "gsl_post",
            "political_outlook",
            "people_congress_post",
            "cppcc_post",
            "gsl_post_chose",
            "social_position",
            "no_post",
            "model_worker",
            "may_day_medal",
            "red_flag_bearer",
            "may_4t_medal",
            "excellent_bbuilder",
            "glory_medal",
            "order_awards",
            "no_social_honor",
            "curriculum_vitae",
            "update_by",
            "update_date",
            "create_by",
            "create_date",
            "del_flag",
            "remarks",
            "scope",
            "card_id",
            "people_congress_post_country",
            "people_congress_post_province",
            "people_congress_post_city",
            "people_congress_post_county",
            "cppcc_post_country",
            "cppcc_post_province",
            "cppcc_post_city",
            "cppcc_post_county",
            "gsl_post_chose_country",
            "gsl_post_chose_province",
            "gsl_post_chose_city",
            "gsl_post_chose_county",
            "zw_id",
            "dl_card",
            "sxzzbx"
    );

    public static final List<String> enterpriseFieldList = Arrays.asList(
            "id",
            "name_enterprise",
            "legal_representative",
            "enterprise_scale",
            "credit_code",
            "qy_scale",
            "insurance_number",
            "industry_classification",
            "postal_address",
            "postcode",
            "main_business",
            "qyjy_registered_capital",
            "qyjy_asset_load_ratio",
            "qyjy_total_assets",
            "qyjy_business_income",
            "qyjy_net_assets",
            "qyjy_total_profit",
            "qyjy_tax_amount",
            "zg_count",
            "zg_labor_contract",
            "zg_collective_contract",
            "dzz_is_dzz",
            "dzz_date",
            "dzz_chose",
            "dzz_count",
            "labour_union",
            "tzz_date",
            "tzz",
            "gysy",
            "qy_characteristic",
            "qy_info",
            "update_by",
            "update_date",
            "create_by",
            "create_date",
            "del_flag",
            "remarks",
            "zw_id",
            "scope",
            "card_id",
            "five_year",
            "sj_dzz_name",
            "dzz_sj_name",
            "dzz_job",
            "yfzxsl_country",
            "yfzxsl_province",
            "yfzxsl_city",
            "jkjbjsl_country",
            "jkjbjsl_province",
            "jkjbjsl_city",
            "is_gxjsqy",
            "patent_number",
            "yffy",
            "zjfs",
            "dzzbzcy",
            "dzzlxrxm",
            "lxdh",
            "zjtxqy_country",
            "zjtxqy_province",
            "yxgjqy",
            "cylzqy",
            "zzydxgjqy",
            "cfwbqqy",
            "ssgs",
            "zgmywbqqy",
            "gdbqmyqy",
            "qsbqqy",
            "bdltqy",
            "gmysgyqy",
            "qyjylytd",
            "myqyrck"
    );
}
